package org.compiere.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.apps.ADialog;
import org.compiere.model.MBPartner;
import org.compiere.model.MConversionType;
import org.compiere.model.MCurrency;
import org.compiere.model.MFactAcct;
import org.compiere.model.MGLCategory;
import org.compiere.model.MJournal;
import org.compiere.model.MJournalBatch;
import org.compiere.model.MJournalLine;
import org.compiere.model.MPeriod;
import org.compiere.model.MSequence;
import org.compiere.model.MTable;
import org.compiere.model.X_C_ElementValue;
import org.compiere.model.X_C_ValidCombination;
import org.compiere.model.X_FI_ClosePlanLine;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.CLogger;

public class FI_ReceivablePayableAccounts extends SvrProcess 
{
	private int FI_ClosePlan_ID;
	
	private int C_Period_ID;
	private int C_AcctSchema_ID;
	private int C_DocType_ID;
	
	private int AD_Table_ID;
	private int CreditAccount_ID;
	private int DebitAccount_ID;
	
	String journalDescription = "Закрытие дебиторской/кредиторской задолженности";
	
	private String trx;
	private Properties ctx;
	
	private CLogger Log = CLogger.getCLogger(IncomeExpedentureCloseMonth.class);
	
	MJournalBatch batch;
	MPeriod period;
	@Override
	protected void prepare() 
	{
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null); 
			else if (para[i].getParameterName().compareTo("C_Period_ID") == 0)
			{
				C_Period_ID = Integer.parseInt(para[i].getParameter().toString());
			}
			else if (para[i].getParameterName().compareTo("C_AcctSchema_ID") == 0)
			{
				C_AcctSchema_ID = Integer.parseInt(para[i].getParameter().toString());
			}
			else if (para[i].getParameterName().compareTo("FI_ClosePlan_ID") == 0)
			{
				FI_ClosePlan_ID = Integer.parseInt(para[i].getParameter().toString());
			}
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}
		}
		
		trx = get_TrxName();
		ctx = getCtx();
		
		period = new MPeriod(ctx, C_Period_ID, trx);
		
		AD_Table_ID = MTable.getTable_ID("GL_Journal");
	}

	private boolean isHasJournals;
	
	private BigDecimal totalDebit;
	private BigDecimal totalCredit;
	
	@SuppressWarnings("static-access")
	@Override
	protected String doIt() throws Exception 
	{		
		ArrayList<Integer> lines = getPlanLines();
		
		C_DocType_ID = getC_DocType();

		String oldBatchsql = "SELECT GL_JournalBatch_ID FROM GL_JournalBatch WHERE C_DocType_ID = " + C_DocType_ID + 
								" AND C_Period_ID = " + C_Period_ID + " AND DocStatus = 'CO'";
		
		int oldBatch_ID = DB.getSQLValue(trx, oldBatchsql);
		
		if(oldBatch_ID != -1)
		{
			ADialog dialog = new ADialog();
			
			if(dialog.ask(25, null, "Процесс \"Закрытие месяца. ДТ/КТ\" уже был успешно завершен. Вы хотите запустить данный процесс повторно?"))
			{
				ReClose(oldBatch_ID);
			}
			else
			{
				return "Операция отменена";
			}
		}
		
		batch = CreateBatch();
		
		totalDebit = new BigDecimal(0);
		totalCredit = new BigDecimal(0);
		
		for(int i = 0; i < lines.size(); i++)
		{		
			X_FI_ClosePlanLine line = new X_FI_ClosePlanLine(ctx, lines.get(i), trx);
			X_C_ElementValue creditValue = new X_C_ElementValue(ctx, line.getCreditAccount_ID(), trx);
			X_C_ElementValue debitValue = new X_C_ElementValue(ctx, line.getDebitAccount_ID(), trx);
			
			String sql = getSql(creditValue.getValue(), debitValue.getValue());		

			CreditAccount_ID = creditValue.get_ID();
			DebitAccount_ID = debitValue.get_ID();			
			
			ClosePeriod(sql);
		}
		
		if(isHasJournals)
		{
			batch.setTotalDr(totalDebit);
			batch.setTotalCr(totalCredit);
			batch.setDocStatus(MJournalBatch.ACTION_Complete);
			batch.setDocAction(MJournalBatch.ACTION_Complete);
			batch.completeIt();
			batch.save();
		}
		else
			batch.delete(true);
		return batch.getDocumentNo();
	}

	private ArrayList<Integer> getPlanLines() throws SQLException
	{
		ArrayList<Integer> lines = new ArrayList<Integer>();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{			
			pstmt = DB.prepareStatement("SELECT FI_ClosePlanLine_ID FROM FI_ClosePlanLine WHERE FI_ClosePlan_ID = " + FI_ClosePlan_ID, trx);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				lines.add(rs.getInt(1));
			}
		}
		catch(Exception ex) 
		{
		}
		finally
		{
			rs.close(); pstmt.close();
			rs = null; pstmt = null;
		}
		
		return lines;
	}
	
	private String getSql(String accountCredit, String accountDebit) throws SQLException
	{
		StringBuffer sql = new StringBuffer();
		
		sql.append("WITH MAIN1 AS (SELECT SUM(f.AmtSourceCr - f.AmtSourceDr) as Saldo_End, f.C_Bpartner_ID, f.UserElement1_ID FROM Fact_Acct f");
		sql.append(" \nINNER JOIN C_ElementValue e ON e.C_ElementValue_ID = f.account_ID");
		sql.append(" \nWHERE e.Value LIKE '" + accountCredit + "%'");
		sql.append(" \nAND f.DateAcct <= '" + period.getEndDate() + "'");
		sql.append(" \nGROUP BY f.C_Bpartner_ID, f.UserElement1_ID),");
		sql.append(" \nMain2 AS(");
		sql.append(" \nSELECT SUM(f.AmtSourceDr - f.AmtSourceCr) as Saldo_End, f.C_Bpartner_ID, f.UserElement1_ID FROM Fact_Acct f");
		sql.append(" \nINNER JOIN C_ElementValue e ON e.C_ElementValue_ID = f.account_ID");
		sql.append(" \nWHERE e.Value LIKE '" + accountDebit + "%'");
		sql.append(" \nAND f.DateAcct <= '" + period.getEndDate() + "'");
		sql.append(" \nGROUP BY f.C_Bpartner_ID, f.UserElement1_ID)");
		sql.append(" \nSELECT Main1.Saldo_End, Main2.Saldo_End, Main1.C_BPartner_ID, Main1.UserElement1_ID FROM Main1");
		sql.append(" \nINNER JOIN Main2 ON Main2.C_BPartner_ID = Main1.C_BPartner_ID AND Main2.UserElement1_ID = Main1.UserElement1_ID");
		sql.append(" \nWHERE Main1.Saldo_End != Main2.Saldo_End AND Main1.Saldo_End != 0 AND Main2.Saldo_End != 0");
		sql.append(" \nORDER BY Main1.C_BPartner_ID, Main1.UserElement1_ID");
		
		return sql.toString();
	}
	
	private void ClosePeriod(String sql) throws SQLException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try
		{			
			BigDecimal debit = null;
			BigDecimal credit = null;
				
			pstmt = DB.prepareStatement(sql, trx);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				credit = rs.getBigDecimal(1);
				debit = rs.getBigDecimal(2);
				
				if(credit.compareTo(debit) == 0 || credit.doubleValue() == 0 || debit.doubleValue() == 0)
				{
					continue;
				}
				
				totalDebit = totalDebit.add(debit);
				totalCredit = totalCredit.add(credit);
				
				CreateJournal(debit.doubleValue() > credit.doubleValue() ? credit : debit, rs.getInt(3), rs.getInt(4));
			}
			
			
		}
		catch(Exception ex) 
		{
		}
		finally
		{
			rs.close(); pstmt.close();
			rs = null; pstmt = null;
		}
	}
	
	private int getCombination(int C_BPartner_ID, int account)
	{
		String sql = "SELECT MAX(C_ValidCombination_ID) FROM C_ValidCombination WHERE C_BPartner_ID = " + C_BPartner_ID + " AND Account_ID = " + account;
		
		int C_ValidCombination_ID = DB.getSQLValue(trx, sql);
		
		if(C_ValidCombination_ID <= 0)
		{
			X_C_ValidCombination combination = new X_C_ValidCombination(ctx, 0, trx);
			combination.setAccount_ID(account);
			combination.setC_AcctSchema_ID(C_AcctSchema_ID);
			combination.setC_BPartner_ID(C_BPartner_ID);
			MBPartner bpartner = new MBPartner(ctx, C_BPartner_ID, trx);			
			combination.setCombination(bpartner.getName() + "_" + account);
			
			combination.saveEx();
			
			C_ValidCombination_ID = combination.get_ID();
		}
		
		return C_ValidCombination_ID;
	}
	
	private MJournalBatch CreateBatch()
	{
		MJournalBatch batch = new MJournalBatch(ctx, 0, trx);
		batch.setDescription(journalDescription + period.getName());
		batch.setPostingType(MJournal.POSTINGTYPE_Actual);
		batch.setC_DocType_ID(C_DocType_ID);
		batch.setDateDoc(period.getEndDate());
		batch.setDateAcct(period.getEndDate());
		batch.setC_Period_ID(period.get_ID());
		batch.setGL_Category_ID(MGLCategory.getDefaultSystem(ctx).get_ID());
		batch.setC_Currency_ID(MCurrency.get(ctx, "KZT").get_ID());
		batch.setProcessed(false);
		batch.setDocStatus(MJournalBatch.DOCSTATUS_Drafted);
		batch.setDocAction(MJournalBatch.ACTION_Complete);
		batch.save();
		
		return batch;
	}
	
	private int getC_DocType()
	{
		String sql = "SELECT C_DocType_ID FROM C_DocType WHERE Name = 'Сlosing of debtor and creditor debt'";
		
		int C_DocType_ID = DB.getSQLValue(trx, sql);
		
		return C_DocType_ID;
	}
	
	private void CreateJournal(BigDecimal minValue, int C_BPartner_ID, int CMS_Contract_ID)
	{
		if(minValue.doubleValue() == 0)
			return;
		
		isHasJournals = true;
		
		String dn = MSequence.getDocumentNo (Env.getAD_Client_ID(ctx), "GL_Journal", trx, null);
		
		MJournal journal = new MJournal(ctx, 0, trx);
		journal.setClientOrg(getAD_Client_ID(), Env.getAD_Org_ID(ctx));
		journal.setGL_JournalBatch_ID(batch.get_ID());
		journal.setC_Period_ID(C_Period_ID);
		journal.setDateDoc(period.getEndDate());
		journal.setDateAcct(period.getEndDate());		
		journal.setDocumentNo(dn);
		journal.setDescription(journalDescription);		
		journal.setC_DocType_ID(C_DocType_ID);
		journal.setC_AcctSchema_ID(C_AcctSchema_ID);
		journal.setGL_Category_ID(batch.getGL_Category_ID());
		journal.setC_Currency_ID(batch.getC_Currency_ID());
		journal.setC_ConversionType_ID(MConversionType.getDefault(Env.getAD_Client_ID(ctx)));
		journal.setDocStatus(MJournalBatch.STATUS_InProgress);
		journal.setDocAction(MJournalBatch.ACTION_None);
		journal.setIsPrinted(false);
		journal.setPostingType(batch.getPostingType());
		journal.setIsActive(true);
		journal.setC_AcctSchema_ID(C_AcctSchema_ID);
		journal.setC_DocType_ID(batch.getC_DocType_ID());
		journal.setCurrencyRate(new BigDecimal(1));		
		journal.setProcessing(false);
		journal.setProcessed(false);
		journal.setPosted(true);
		journal.save();
		
		CreateLine(journal, CreditAccount_ID, new BigDecimal(0), minValue, C_BPartner_ID, CMS_Contract_ID, 10);
		CreateLine(journal, DebitAccount_ID, minValue, new BigDecimal(0), C_BPartner_ID, CMS_Contract_ID, 20);
		
		journal.setTotalCr(minValue);
		journal.setTotalDr(minValue);
		journal.completeIt();
		journal.saveEx();
	}
	
	private void CreateLine(MJournal journal, int account, BigDecimal credit, BigDecimal debit, int C_BPartner_ID, int CMS_Contract_ID, int lineValue)
	{
		int C_ValidCombination_ID = getCombination(C_BPartner_ID, account);
		MJournalLine line = new MJournalLine(ctx, 0, trx);
		line.setC_ValidCombination_ID(C_ValidCombination_ID);
		line.setAmtSourceCr(credit);
		line.setAmtSourceDr(debit);
		line.setCMS_Contract_ID(CMS_Contract_ID);
		line.setLine(lineValue);
		line.setGL_Journal_ID(journal.get_ID());
		line.setC_Currency_ID(journal.getC_Currency_ID());
		line.setC_ConversionType_ID(journal.getC_ConversionType_ID());
		line.saveEx();
		
		CreateFact(journal, line, C_BPartner_ID, CMS_Contract_ID, account);
	}
	
	private void CreateFact(MJournal journal, MJournalLine line, int C_BPartner_ID, int CMS_Contract_ID, int account)
	{
		MFactAcct fact = new MFactAcct(ctx, 0, trx);					

		fact.setAD_Org_ID(Env.getAD_Org_ID(ctx));
		fact.setC_AcctSchema_ID(journal.getC_AcctSchema_ID());
		fact.setC_Period_ID(journal.getC_Period_ID());		
		fact.setAmtSourceCr(line.getAmtSourceCr());
		fact.setAmtSourceDr(line.getAmtSourceDr());
		fact.setAmtAcctCr(line.getAmtAcctCr());
		fact.setAmtAcctDr(line.getAmtAcctDr());
		fact.setDateAcct(journal.getDateAcct());
		fact.setDateTrx(journal.getDateDoc());
		fact.setRecord_ID(journal.get_ID());
		fact.setC_BPartner_ID(C_BPartner_ID);
		fact.setAD_Table_ID(AD_Table_ID);
		fact.setAccount_ID(account);
		fact.setLine_ID(line.getLine());
		fact.setPostingType(journal.getPostingType());
		fact.setC_Currency_ID(journal.getC_Currency_ID());
		fact.setUserElement1_ID(CMS_Contract_ID);
		
		if(!fact.save())
			Log.log(Level.SEVERE, "Fact Acct is not saved");
	}
	
	private void ReClose(int oldBatch_ID)
	{		
		MJournalBatch batch = new MJournalBatch(ctx, oldBatch_ID, trx);
		MJournalBatch newBatch = MJournalBatch.copyFrom(ctx, batch.get_ID(), batch.getDateAcct(), trx);
		
		MJournal[] journals = newBatch.getJournals(false);
		MJournal[] oldJournals = batch.getJournals(false);
		if(!newBatch.save())
			Log.log(Level.SEVERE, "MJournalBatch reclose is not saved");
		
		for(int i = 0; i < journals.length; i++)
		{
			MJournalLine[] lines = journals[i].getLines(false);
			
			for(int j = 0; j < lines.length; j++)
			{
				X_C_ValidCombination combination = new X_C_ValidCombination(ctx, lines[j].getC_ValidCombination_ID(), trx);
				
				lines[j].setAmtSourceCr((new BigDecimal(0)).subtract(lines[j].getAmtSourceCr()));
				lines[j].setAmtSourceDr((new BigDecimal(0)).subtract(lines[j].getAmtSourceDr()));
								
				if(!lines[j].save())
					Log.log(Level.SEVERE, "MJournalLine reclose is not saved");
				
				CreateFact(journals[i], lines[j], combination.getC_BPartner_ID(), lines[j].getCMS_Contract_ID(), combination.getAccount_ID());
			}
			
			journals[i].setTotalCr((new BigDecimal(0).subtract(journals[i].getTotalCr())));
			journals[i].setTotalDr((new BigDecimal(0).subtract(journals[i].getTotalDr())));
			journals[i].setDocStatus(MJournalBatch.ACTION_Void);
			journals[i].setDocAction(MJournalBatch.ACTION_None);
			journals[i].setProcessing(false);
			journals[i].setProcessed(false);
			journals[i].setProcessed(false);
			journals[i].setPosted(true);
			journals[i].completeIt();
			journals[i].voidIt();
			
			journals[i].saveEx();
			
			//oldJournals[i].reActivateIt();
			oldJournals[i].setPosted(true);
			oldJournals[i].setDocStatus(MJournal.ACTION_ReActivate);
			oldJournals[i].setDocAction(MJournal.ACTION_Reject);
			oldJournals[i].setProcessed(true);
			if(!oldJournals[i].save())
				Log.log(Level.SEVERE, "MJournalLine reclose is not saved");
		}
		
		newBatch.setTotalCr((new BigDecimal(0).subtract(batch.getTotalCr())));
		newBatch.setTotalDr((new BigDecimal(0).subtract(batch.getTotalDr())));
		newBatch.setDocStatus(MJournalBatch.ACTION_Void);
		newBatch.setDocAction(MJournalBatch.ACTION_Reject);
		newBatch.voidIt();
		newBatch.setProcessed(true);
		if(!newBatch.save())
			Log.log(Level.SEVERE, "MJournalBatch reclose is not saved");
		
		batch.setDocStatus(MJournal.ACTION_ReActivate);
		batch.setDocAction(MJournal.ACTION_Reject);
		batch.reActivateIt();
		batch.setProcessed(true);
		if(!batch.save())
			Log.log(Level.SEVERE, "MJournalBatch reclose is not saved");
	}
}
